package com.meeting.y.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.NumberFormat;

public class OfficeUtil {


    /**
     * 获取表头对应的列数
     * @param headname
     * @return
     */
    public static Integer[] getHeadNums(int headline,String[] headname,Sheet sheet){
        Integer[] headNums=new Integer[headname.length];
        Row row = sheet.getRow(headline);//获取表头 从0开始的
        String str="";
        int count = 0;
        for (int i = 0; i < headname.length; i++) {
            for (int rnum=0; rnum < 20; rnum++) {// 只判断前 20列
                str = getCellValue(row.getCell(rnum));
                if (StringUtil.isStrEmpty(str)) {
                    continue;
                }
                if (str.trim().equals(headname[i])) {
                    count++;
                    headNums[i]=rnum;
                    break;
                }
            }
        }
        if(count == headname.length){
            return headNums;
        }else {
            return null;
        }
    }

    public static String getCellValue(Cell cell){
        NumberFormat formater = NumberFormat.getInstance();
        formater.setGroupingUsed(false);
        String cellValue="";
        if(cell==null){
            return cellValue;
        }
        switch(cell.getCellType()){
            case Cell.CELL_TYPE_NUMERIC:
                java.math.BigDecimal big = new java.math.BigDecimal(cell.getNumericCellValue());
                cellValue = StringUtil.safeToString(big.toString(), "");
                break;
            case Cell.CELL_TYPE_FORMULA:
                try {
                    cellValue = cell.getStringCellValue();
                } catch (IllegalStateException e) {
                    java.math.BigDecimal big1 = new java.math.BigDecimal(cell.getNumericCellValue());
                    cellValue = StringUtil.safeToString(big1.toString(), "");
                }
                break;
            case HSSFCell.CELL_TYPE_STRING:
                cellValue = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                cellValue = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                cellValue = Boolean.valueOf(cell.getBooleanCellValue()).toString();
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                cellValue = String.valueOf(cell.getErrorCellValue());
                break;
            default:cellValue="";
        }
        return cellValue.trim();
    }

    public static Workbook parse(InputStream buffer, String suffix) {
        Workbook text = null;
        switch (suffix) {
            case "xls":
                text = getTextFromExcel(buffer);
                break;
            case "xlsx":
                text = getTextFromExcel2007(buffer);
                break;
        }
        return text;
    }

    // 读取Excel97-2003的全部内容 xls
    private static Workbook getTextFromExcel(InputStream file) {
        Workbook wb = null;
        String text = "";
        try {
            wb = new HSSFWorkbook(new POIFSFileSystem(file));
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    // 读取Excel2007+的全部内容 xlsx
    private static Workbook getTextFromExcel2007(InputStream file) {
        Workbook workBook = null;
        String text = "";
        try {
            workBook = new XSSFWorkbook(file);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workBook;
    }

    /**
     * Remove a row by its index
     * @param sheet a Excel sheet
     * @param rowIndex a 0 based index of removing row
     */
    public static void removeRow(Sheet sheet, int rowIndex) {
        int lastRowNum=sheet.getLastRowNum();
        if(rowIndex>=0&&rowIndex<lastRowNum)
            sheet.shiftRows(rowIndex+1,lastRowNum,-1);//将行号为rowIndex+1一直到行号为lastRowNum的单元格全部上移一行，以便删除rowIndex行
        if(rowIndex==lastRowNum){
            Row removingRow=sheet.getRow(rowIndex);
            if(removingRow!=null)
                sheet.removeRow(removingRow);
        }
    }

    public static Row addRow(Sheet sheet,int rowIndex){
        sheet.shiftRows( rowIndex, sheet.getLastRowNum(), 1, true, false);
        Row row = sheet.getRow(rowIndex)==null?sheet.createRow(rowIndex):sheet.getRow(rowIndex);
        return row;
    }

}
